﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using HotelMatchUtility.Entity;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Arch.Framework.Utility;
using System.Data.Common;
using System.Data;

namespace HotelMatchUtility.Data
{
    public class OtherData
    {
        public static void update(string sp)
        {
            Database db = null;
            db = DatabaseFactory.CreateDatabase("ProductDB_SELECT_pro");
            DbCommand dbCommand = db.GetSqlStringCommand(sp);
            dbCommand.CommandTimeout = 0;
            db.ExecuteNonQuery(dbCommand);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="country"></param>
        /// <param name="GroupList">not in GroupList</param>
        /// <returns></returns>
        public  static List<HotelEntity> GetData(int country, List<int> GroupList)
        {
            List<HotelEntity> entityList = new List<HotelEntity>();
            Database db = null;
            db = DatabaseFactory.CreateDatabase("ProductDB_SELECT_pro");
            StringBuilder sql = new StringBuilder();
            sql.Append(@"
SELECT distinct
		case when ih.GroupId = 9 then 'Marriott'
			when ih.GroupId = 11 then 'Hilton'
            when ih.GroupId = 29 then 'JTB'
            when ih.GroupId = 32 then 'Booking'
            when ih.GroupId = 33 then 'Agoda'
            when ih.groupid = 24 then 'IHG'
            when ih.groupid = 31 then 'Accor'
            when ih.groupId = 36 then 'MIKI'
            when ih.groupId = 39 then 'Fourseasons'
            when ih.GroupId = 27 then 'Orbitz'
			else '未知'
		end
	   
	   as 类别
	   , hotel.hotelBelongTo
	   , '' as 有无房价
	   , country.Country,
		country.ename as countryEname,
		country.CountryName,
	    
		Province.Province,
		Province.ProvinceName,
	    
		Resource.City,
		city.CityName,
		city.ename as cityEname,
	    
		hotel.Hotel,
	    
		Resource.zipCode,
	    
		Resource.ResourceName as hotelName,
		Resource_intl.resourcename as hotelEnName,
	    
		isnull(Resource_intl.address,Resource.address) address,
	    
		Resource.telephone,
	    
		Resource.fax,
	    
		resource_map.GLAT,
		resource_map.GLON
		,hotel.hotelGroup
		,ih.GroupId
		,ih.hotelCode
		,hotel.bookable
		,Zone.Zone
		,Zone.ZoneName
		,z2.zoneName as DistrictEnName
        ,Province.Province
        ,Province.ProvinceName
        ,Province.eName as ProvinceEnName
	FROM ProductDB..hotel(nolock)
	INNER JOIN ProductDB..Resource (nolock) on Resource.resource = hotel.Hotel
	INNER JOIN ProductDB..Resource_intl (nolock) on Resource.resource = Resource_intl.resource
	LEFT JOIN ProductDB..resource_map (nolock) on Resource.resource = resource_map.resource
	INNER JOIN ProductDB..city (nolock) on City.City = Resource.City
	INNER JOIN ProductDB..country (nolock) on city.Country = Country.Country
	LEFT JOIN ProductDB..Province (nolock) on Province.country = country.country and Province.Province = city.Province
	LEFT JOIN HotelInterfaceDB..hotelInterface_hotelGroupInfo ih (nolock) on ih.resource = hotel.Hotel
	Left join Productdb..Zone(nolock) on Zone.Zone = Hotel.Zone
    Left join Productdb..ZoneINtl(nolock) z2 on z2.Zone = Hotel.Zone
	where  resource.Type = 'H' and Country.Country = @Country 
    and hotel.HotelBelongTo = 'HTL'	and hotel.hotelGroup<>5
    And (country.country <> 1)-- or Province.Province = 32 or Province.Province = 33 or Province.Province=53)
    --and resource.resource = '47220'
            ");
            int countGroupId = 0;
            if (GroupList.Count > 0)
            {
                foreach (var GroupId in GroupList)
                {
                    if (countGroupId == 0)
                    {
                        sql.Append("And ih.GroupId not in (");
                        sql.Append(GroupId);
                    }
                    else
                    {
                        sql.Append(",").Append(GroupId);
                    }
                    if (countGroupId == GroupList.Count - 1)
                    {
                        sql.Append(")");
                    }
                    countGroupId++;
                }
            }

            HotelEntity classEntity = null;


            DbCommand dbCommand = db.GetSqlStringCommand(sql.ToString());
            dbCommand.CommandTimeout = 0;
            //db.AddInParameter(dbCommand, "@GroupID", DbType.Int32, groupid);
            db.AddInParameter(dbCommand, "@Country", DbType.Int32, country);
            using (IDataReader idr = db.ExecuteReader(dbCommand))
            {
                while (idr.Read())
                {
                    if (idr["类别"].ToString() == "未知" && idr["Bookable"].ToString() == "F") continue;
                    classEntity = new HotelEntity();
                    classEntity.Hotel = ConvertHelper.ToInt32(idr["Hotel"]);
                    classEntity.HotelName = ConvertHelper.ToString(idr["HotelName"]).Trim();
                    classEntity.HotelEnName = ConvertHelper.ToString(idr["HotelEnName"]).Trim();
                    classEntity.Address = ConvertHelper.ToString(idr["Address"]).Trim();
                    classEntity.Telephone = ConvertHelper.ToString(idr["Telephone"]).Trim();
                    classEntity.ZipCode = ConvertHelper.ToString(idr["ZipCode"]).Trim();
                    classEntity.City = ConvertHelper.ToInt32(idr["City"]);
                    classEntity.CityName = ConvertHelper.ToString(idr["CityName"]).Trim();
                    //classEntity.CityEnName = Convert.ToString(idr["CityEnName"]);
                    classEntity.CityEnName = Convert.ToString(idr["cityEname"]).Trim();

                    classEntity.Country = ConvertHelper.ToInt32(idr["Country"]);
                    classEntity.CountryName = ConvertHelper.ToString(idr["CountryName"]).Trim();
                    classEntity.CountryEnName = Convert.ToString(idr["countryEname"]).Trim();
                    classEntity.Glat = Convert.ToDouble(idr["Glat"] == DBNull.Value ? 0 : idr["Glat"]);
                    classEntity.Glon = Convert.ToDouble(idr["Glon"] == DBNull.Value ? 0 : idr["Glon"]);

                    classEntity.HotelGroup = Convert.ToInt32(idr["hotelGroup"]);
                    classEntity.HotelBelongTo = ConvertHelper.ToString(idr["HotelBelongTo"]).Trim();

                    classEntity.HotelType = ConvertHelper.ToString(idr["类别"]).Trim();
                    //classEntity.HotelGroupName = ConvertHelper.ToString(idr["HotelGroupName"]).Trim();
                    classEntity.Bookable = ConvertHelper.ToString(idr["Bookable"]).Trim();
                    //classEntity.VendorID = ConvertHelper.ToInt32(idr["VendorID"]);
                    //classEntity.VendorName = ConvertHelper.ToString(idr["VendorName"]);
                    classEntity.PriceInfo = ConvertHelper.ToString(idr["有无房价"]).Trim();

                    classEntity.DistrictCodeBookingOrCtrip = ConvertHelper.ToString(idr["zone"].ToString());
                    classEntity.DistrictEnName = ConvertHelper.ToString(idr["DistrictEnName"]);
                    classEntity.DistrictName = ConvertHelper.ToString(idr["zonename"]);

                    classEntity.Province = ConvertHelper.ToInt32(idr["Province"]);
                    classEntity.ProvinceEnName = ConvertHelper.ToString(idr["ProvinceEnName"]);
                    classEntity.ProvinceName = ConvertHelper.ToString(idr["ProvinceName"]);
                    
                    entityList.Add(classEntity);
                }
            }
            return entityList;

        }
    }
}
